package cn.neusoft.dao.impl;

import cn.neusoft.bean.Food;
import cn.neusoft.bean.User;
import cn.neusoft.dao.IUserDao;
import cn.neusoft.query.BaseQuery;
import cn.neusoft.query.FoodQuery;
import cn.neusoft.query.UserQuery;
import cn.neusoft.utils.JdbcUtils;
import com.alibaba.druid.util.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao implements IUserDao {
    private QueryRunner queryRunner;

    public UserDao() {
        queryRunner = JdbcUtils.getQueryRunner();
    }

    @Override
    public int add(User user) throws SQLException {
        String sql ="INSERT INTO user(userId, password, userName, userSex, userImg, delTag) VALUES (?,?,?,?,?,?)";
        return queryRunner.update(sql,
                user.getUserId(),
                user.getPassword(),
                user.getUserName(),
                user.getUserSex(),
                user.getUserImg(),
                user.getDelTag());
    }

    @Override
    public int update(User user) throws SQLException {
        String sql = "update user set userId=?,password=?,userName=?,userSex=?,userImg=?,delTag=? where id=?";
        return queryRunner.update(sql,
                user.getUserId(),
                user.getPassword(),
                user.getUserName(),
                user.getUserSex(),
                user.getUserImg(),
                user.getDelTag(),
                user.getId());
    }

    @Override
    public int delete(Integer id) throws SQLException {
        String sql = "delete from user where id=?";
        return queryRunner.update(sql,id);
    }

    @Override
    public User getById(Integer id) throws SQLException {
        String sql = "select id,userId,password,userName,userSex,userImg,delTag from user where id =?";
        return queryRunner.query(sql,new BeanHandler<User>(User.class),id);
    }

    @Override
    public List<User> getAll() throws SQLException {
        String sql = "select id,userId,password,userName,userSex,userImg,delTag from user";
        return queryRunner.query(sql,new BeanListHandler<User>(User.class));
    }

    @Override
    public int getCount(BaseQuery baseQuery) throws SQLException {
        UserQuery query = (UserQuery) baseQuery;
        List<Object> params = new ArrayList<>();
        String sql = "select count(id) from user where 1=1 ";
        if(!StringUtils.isEmpty(query.getUserName())) {
            sql += " and userName like concat('%',?,'%')";
            params.add(query.getUserName());
        }
        System.out.println("查询总记录："+sql);
        System.out.println("参数集合："+params);

        return queryRunner.query(sql, new ScalarHandler<Long>(), params.toArray()).intValue();
    }

    @Override
    public List<User> getByPage(BaseQuery baseQuery) throws SQLException {
        UserQuery query = (UserQuery) baseQuery;
        List<Object> params = new ArrayList<>();
        String sql = "select id,userId,password,userName,userSex,userImg,delTag from user" +
                " where 1=1";
        if(!StringUtils.isEmpty(query.getUserName())) {
            sql += " and userName like concat('%',?,'%')";
            params.add(query.getUserName());
        }
        sql += " ORDER BY id DESC limit ?,?";
        params.add(query.getStartIndex());
        params.add(query.getPageCount());

        System.out.println("查询集合："+sql);
        System.out.println("参数集合："+params);

        return queryRunner.query(sql, new BeanListHandler<User>(User.class), params.toArray());
    }
}
